from sqlalchemy.dialects.mysql import JSON
from sqlmodel import (
    Column,
    String,
    CHAR
)
from sqlmodel import Field

from entity.model.common_model import BaseCommonModel, UnsignedBigInt, UnsignedInt


# 房源管理-楼盘表
class CommunityProjectRecord(BaseCommonModel, table=True):
    __tablename__ = "community_project_record"
    id: int = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    province: str = Field(
        sa_column=Column(String(150), comment="省份")
    )
    area: str = Field(
        sa_column=Column(String(150), comment="城市")
    )
    county: str = Field(
        sa_column=Column(String(150), comment="区域")
    )
    province_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="省份ID")
    )
    area_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="城市ID")
    )
    county_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="区域ID")
    )
    community_id: str = Field(description="楼盘链接id", nullable=False, unique=True)
    unsigned_contract: int = Field(
        sa_column=Column(UnsignedInt, default=0, comment="未网签套数")
    )
    data_source: int = Field(
        sa_column=Column(UnsignedInt, default=1, comment="数据来源:1-采集,0-购买")
    )
    # name: str = Field(nullable=True, description="楼盘名称")
    # address: str = Field(nullable=True, description="地址")
    # area: str = Field(nullable=True, description="所在区县")
    # developer: str = Field(nullable=True, description="开发商")

# 房源管理-楼栋表
class CommunityBuildingRecord(BaseCommonModel, table=True):
    __tablename__ = "community_building_record"
    id: int = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    province: str = Field(
        sa_column=Column(String(150), comment="省份")
    )
    area: str = Field(
        sa_column=Column(String(150), comment="城市")
    )
    county: str = Field(
        sa_column=Column(String(150), comment="区域")
    )
    province_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="省份ID")
    )
    area_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="城市ID")
    )
    county_id: int = Field(
        sa_column=Column(UnsignedBigInt, comment="区域ID")
    )
    community_name: str = Field(
        sa_column=Column(String(150), comment="小区名称")
    )
    community_id: str = Field(
        sa_column=Column(String(100), comment="采集到的小区id")
    )
    address: str = Field(
        sa_column=Column(String(255), comment="地址")
    )
    developer: str = Field(
        sa_column=Column(String(120), comment="开发商")
    )
    building_id: str = Field(
        sa_column=Column(String(100), comment="采集到楼栋id", unique=True)
    )
    regulatory_banks: str = Field(
        sa_column=Column(String(100), comment="监管银行")
    )
    regulatory_account: str = Field(
        sa_column=Column(String(100), comment="监管账号")
    )
    certificate_no: str = Field(
        sa_column=Column(String(100), comment="证书编号")
    )
    building_number: str = Field(
        sa_column=Column(String(100), comment="楼栋号")
    )
    is_house: str = Field(
        sa_column=Column(CHAR(1), default='0', comment="楼栋类型，0：住宅, 1: 其他")
    )
    can_sale_count: int = Field(
        sa_column=Column(UnsignedInt, comment="可售数量", default=0)
    )
    sale_count: int = Field(
        sa_column=Column(UnsignedInt, comment="已售数量", default=0)
    )
    data_source: int = Field(
        sa_column=Column(UnsignedInt, default=1, comment="数据来源:1-采集,0-购买")
    )


class CrawlerDataRecord(BaseCommonModel, table=True):
    __tablename__ = 'crawler_data_record'

    # 主键ID
    id: int = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    # 城市
    city: str = Field(sa_column=Column(String(255), nullable=False))

    # 爬取类型
    crawler_type: str = Field(sa_column=Column(String(255), nullable=False))

    # 爬取数量
    crawler_total: int = Field(sa_column=Column(UnsignedBigInt, nullable=False, default=0))

    # 成交日期
    transaction_time: str = Field(sa_column=Column(CHAR(10)))

    # 其他类型时间
    other_time: str = Field(sa_column=Column(CHAR(10)))

class CrawlerCityRecord(BaseCommonModel, table=True):
    __tablename__ = 'crawler_city_record'
    # 主键ID
    id: int | None = Field(
        sa_column=Column(UnsignedBigInt, primary_key=True, nullable=False, comment="主键ID")
    )
    # 城市
    city: str = Field(sa_column=Column(String(255), nullable=False))
    data: str = Field(sa_column=Column(JSON))
    http_url: str = Field(sa_column=Column(String(500), unique=True))
    # status: str
# 房源管理-单元表
# class UnitRecord(BaseCommonModel, table=True):
#     __tablename__ = "unit_record"
#     id: int = Field(default=None, description="主键", sa_column=Column(
#             BigUnsignedBigInt,
#             primary_key=True,
#             autoincrement=True,
#             comment="主键id",
#         ))
#     community_id: int = Field(description="所属楼盘id")
#     building_id: int = Field(description="所属楼栋id")
#     unit_number: str = Field(description="单元号", nullable=True)
#     total_room_number: int = Field(description="总室号")
#     total_floor: int = Field(description="总楼层", nullable=True)
#     lift: int = Field(nullable=True)
#     household: int = Field(nullable=True)
#     room_number_start: str = Field(nullable=True)
#     room_number_end: str = Field(nullable=True)

# 房源管理-户室表
# class RoomRecord(BaseCommonModel, table=True):
#     __tablename__ = "room_record"
#     id: int = Field(default=None, description="主键", sa_column=Column(
#             BigUnsignedBigInt,
#             primary_key=True,
#             autoincrement=True,
#             comment="主键id",
#         ))
#     room_data_id: int = Field(description="房间数据id")
#     room: str = Field(default="室号")
#     floor: int = Field(default=None)
#     room_number: int = Field(description="户室号")
#     building_area: Decimal = Field(default=None, description="房屋面积")
#     interior_area: Decimal = Field(default=None, description="套内面积")
#     public_area: Decimal = Field(default=None, description="公摊面积")
#     use_type: str = Field(nullable=True, description="房屋用途")
#     state: int = Field(description="销售状态")

# class RoomRecord(BaseCommonModel, table=True):
#     __tablename__ = "room_record"
#     id: int = Field(default=None, description="主键", sa_column=Column(
#             BigUnsignedBigInt,
#             primary_key=True,
#             autoincrement=True,
#             comment="主键id",
#         ))
#     room: str = Field(default=None)
#     floor: int = Field(default=None)
#     room_number: int = Field(description="户室号")
#     building_area: Decimal = Field(default=None, description="房屋面积")
#     interior_area: Decimal = Field(default=None, description="套内面积")
#     public_area: Decimal = Field(default=None, description="公摊面积")
#     use_type: str = Field(nullable=True, description="房屋用途")
#     state: int = Field(description="销售状态")